package com.yoooya.base.admin.utils;


import org.apache.commons.collections.CollectionUtils;
import org.apache.commons.io.FileUtils;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.*;
import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Date;
import java.util.List;

public class ExcelUtils {

	public static <T> void writeToExcel(String fileName, List<T> data,
			List<String> cnNames, List<String> fieldNames) {

		OutputStream fos = null;
		XSSFWorkbook workbook = null;
		try {
			File file = new File(fileName);
			workbook = new XSSFWorkbook();
			Sheet sheet = workbook.createSheet();
			int rowCount = 0;
			int columnCount = 0;
			Row row = sheet.createRow(rowCount++);
			for (String fieldName : fieldNames) {
				Cell cell = row.createCell(columnCount++);
				cell.setCellValue(fieldName);
			}
			Class<? extends Object> classz = data.get(0).getClass();
			for (T t : data) {
				row = sheet.createRow(rowCount++);
				columnCount = 0;
				for (String fieldName : fieldNames) {
					Cell cell = row.createCell(columnCount);
					Method method = null;
					try {
						method = classz
								.getMethod("get" + capitalize(fieldName));
					} catch (NoSuchMethodException nme) {
						method = classz.getMethod("get" + fieldName);
					}
					Object value = method.invoke(t, (Object[]) null);
					if (value != null) {
						if (value instanceof String) {
							cell.setCellValue((String) value);
						} else if (value instanceof Long) {
							cell.setCellValue((Long) value);
						} else if (value instanceof Integer) {
							cell.setCellValue((Integer) value);
						} else if (value instanceof Double) {
							cell.setCellValue((Double) value);
						}
					}
					columnCount++;
				}
			}
			fos = new FileOutputStream(file);
			workbook.write(fos);
			fos.flush();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				if (fos != null) {
					fos.close();
				}
			} catch (IOException e) {
			}
			try {
				if (workbook != null) {
					workbook.close();
				}
			} catch (IOException e) {
			}
		}
	}

	public static <T> Workbook fromTemplateToWorkBook(String path,
			String fileName, List<T> data, List<String> fieldNames,
			List<String> dictColNames) {
		SimpleDateFormat sdf = new SimpleDateFormat("yyyy.M.d");
		int orderNum = 0;
		Workbook workbook = null;
		try {
			// 读取excel模板
			File file = FileUtils.getFile(path, "temp", fileName);
			InputStream in = new FileInputStream(file);
			// workbook = new XSSFWorkbook(in);
			workbook = WorkbookFactory.create(in);
			Sheet sheet = workbook.getSheetAt(0);
			int rowCount = 2;
			int columnCount = 0;
			Row row = sheet.getRow(rowCount);
			int cellNum = row.getLastCellNum();
			CellStyle[] cellStyles = new CellStyle[cellNum];
			for (int i = 0; i < cellNum; i++) {
				cellStyles[i] = row.getCell(i).getCellStyle();
			}
			final CellStyle rowStyle = row.getRowStyle();
			sheet.removeRow(row);
			Class<? extends Object> classz = data.get(0).getClass();
			for (T t : data) {
				try {
					row = sheet.createRow(rowCount++);
					row.setRowStyle(rowStyle);
					columnCount = 0;
					for (int i = 0; i < fieldNames.size(); i++) {
						String fieldName = fieldNames.get(i);
						Cell cell = row.createCell(columnCount);
						cell.setCellStyle(cellStyles[i]);
						if ("orderNum".equals(fieldName)) {
							cell.setCellValue(++orderNum);
						} else {
							Method method = null;
							try {
								method = classz.getMethod("get"
										+ capitalize(fieldName));
							} catch (NoSuchMethodException nme) {
								method = classz.getMethod("get" + fieldName);
							}
							Object value = method.invoke(t, (Object[]) null);
							if (value != null) {
								if (value instanceof Boolean) {
									String display = (Boolean) value ? "√"
											: null;
									cell.setCellValue(display);
								} else if (dictColNames.contains(fieldName)) {
									if (value instanceof String) {
										if (StringUtils.isNotBlank(value.toString())) {//todo
//											cell.setCellValue(DictMap.dictMap.get(fieldName).get(Integer.valueOf((String) value)));
										}
									} else {
										//todo
//										cell.setCellValue(DictMap.dictMap.get(fieldName).get((Integer) value));
									}
								} else {
									if (value instanceof String) {
										cell.setCellValue((String) value);
									} else if (value instanceof Long) {
										cell.setCellValue((Long) value);
									} else if (value instanceof Integer) {
										cell.setCellValue((Integer) value);
									} else if (value instanceof Double) {
										cell.setCellValue((Double) value);
									} else if (value instanceof Date) {
										cell.setCellValue(sdf
												.format((Date) value));
									}
								}
							}
						}

						columnCount++;
					}
				} catch (Exception e) {
					e.printStackTrace();
				}

			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			return workbook;
		}
	}

	public static <CustomerPo> Workbook genCustomer(String path,
			String fileName, List<CustomerPo> data) {
		List<String> fieldNames = Arrays.asList("region", "name", "contacts",
				"mobile", "issueTime", "intention", "dataSource",
				"taxpayerType", "telephone", "address", "industry", "email",
				"website", "brief");
		List<String> dictColNames = Arrays.asList("taxpayerType");
		SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
		int orderNum = 0;
		Workbook workbook = null;
		InputStream in = null;
		try {
			File file = FileUtils.getFile(path, "temp", fileName);
			in = new FileInputStream(file);
			workbook = WorkbookFactory.create(in);
			Sheet sheet = workbook.getSheetAt(0);
			int rowCount = 1;
			int columnCount = 0;
			Row row = null;
			if (CollectionUtils.isNotEmpty(data)) {
				Class<? extends Object> classz = data.get(0).getClass();
				for (CustomerPo t : data) {
					try {
						row = sheet.createRow(rowCount++);
						columnCount = 0;
						for (int i = 0; i < fieldNames.size(); i++) {
							String fieldName = fieldNames.get(i);
							Cell cell = row.createCell(columnCount);

							Method method = null;
							try {
								method = classz.getMethod("get"
										+ capitalize(fieldName));
							} catch (NoSuchMethodException nme) {
								method = classz.getMethod("get" + fieldName);
							}
							Object value = method.invoke(t, (Object[]) null);
							if (value != null) {
								if (value instanceof Boolean) {
									String display = (Boolean) value ? "√"
											: null;
									cell.setCellValue(display);
								} else if (dictColNames.contains(fieldName)) {
									if (value instanceof String) {
										if (StringUtils.isNotBlank(value.toString())) {
											//todo
//											cell.setCellValue(DictMap.dictMap.get(fieldName).get(Integer.valueOf((String) value)));
										}
									} else {
										//todo
//										cell.setCellValue(DictMap.dictMap.get(fieldName).get((Integer) value));
									}
								} else {
									if (value instanceof String) {
										cell.setCellValue((String) value);
									} else if (value instanceof Long) {
										cell.setCellValue((Long) value);
									} else if (value instanceof Integer) {
										cell.setCellValue((Integer) value);
									} else if (value instanceof Double) {
										cell.setCellValue((Double) value);
									} else if (value instanceof Date) {
										cell.setCellValue(sdf
												.format((Date) value));
									}
								}
							}

							columnCount++;
						}
					} catch (Exception e) {
						e.printStackTrace();
					}

				}
			} else {
				row = sheet.createRow(1);
				row.createCell(13);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			if (in != null) {
				try {
					in.close();
				} catch (IOException e) {
					e.printStackTrace();
				}
			}
			return workbook;
		}
	}

	public static <T> XSSFWorkbook writeToWorkBook(String fileName,
			List<T> data, List<String> cnNames, List<String> fieldNames,
			double[] columnWidths) {

		int orderNum = 0;
		XSSFWorkbook workbook = null;
		try {
			workbook = new XSSFWorkbook();
			Sheet sheet = workbook.createSheet();
			int rowCount = 0;
			int columnCount = 0;
			Row row = sheet.createRow(rowCount++);
			/*
			 * row.setHeight();
			 */
			XSSFCellStyle cellStyle = workbook.createCellStyle();
			for (String cnName : cnNames) {
				Cell cell = row.createCell(columnCount++);
				cell.setCellValue(cnName);
			}
			for (int i = 0; i < columnWidths.length; i++) {
				sheet.setColumnWidth(i, (int) (columnWidths[i] * 1000));
			}
			Class<? extends Object> classz = data.get(0).getClass();
			for (T t : data) {
				row = sheet.createRow(rowCount++);
				columnCount = 0;
				for (String fieldName : fieldNames) {
					Cell cell = row.createCell(columnCount);
					if ("orderNum".equals(fieldName)) {
						cell.setCellValue(++orderNum);
					} else {
						Method method = null;
						try {
							method = classz.getMethod("get"
									+ capitalize(fieldName));
						} catch (NoSuchMethodException nme) {
							method = classz.getMethod("get" + fieldName);
						}
						Object value = method.invoke(t, (Object[]) null);
						if (value != null) {
							if (value instanceof String) {
								cell.setCellValue((String) value);
							} else if (value instanceof Long) {
								cell.setCellValue((Long) value);
							} else if (value instanceof Integer) {
								cell.setCellValue((Integer) value);
							} else if (value instanceof Double) {
								cell.setCellValue((Double) value);
							}
						}
					}

					columnCount++;
				}
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			return workbook;
		}
	}

	// retrieve field names from a POJO class
	private static List<String> getFieldNamesForClass(Class<?> clazz)
			throws Exception {
		List<String> fieldNames = new ArrayList<String>();
		Field[] fields = clazz.getDeclaredFields();
		for (int i = 0; i < fields.length; i++) {
			fieldNames.add(fields[i].getName());
		}
		return fieldNames;
	}

	// capitalize the first letter of the field name for retriving value of the
	// field later
	private static String capitalize(String s) {
		if (s.length() == 0)
			return s;
		return s.substring(0, 1).toUpperCase() + s.substring(1);
	}

}